﻿Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data


Public Class DBTransaction

    Private mmsConString As String = SQLConnectString()
    Private mmsCon As SqlConnection = New SqlConnection(mmsConString)
    Private mmsTransaction As SqlTransaction = Nothing

    Private Function SQLConnectString() As String

        SQLConnectString = "Server=tcp:m77yi2acff.database.windows.net,1433;Database=db01;User ID=linwei@m77yi2acff;Password=fcb326XYZ0;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"

    End Function

    Sub OpenConnection()

        If mmsCon.State = ConnectionState.Closed Then

            mmsCon.Open()

        End If

    End Sub

    Sub CloseConnection()

        If mmsCon.State = ConnectionState.Open Then

            mmsCon.Close()

        End If

    End Sub

    Sub BeginTransaction()

        mmsTransaction = mmsCon.BeginTransaction(IsolationLevel.ReadUncommitted)

    End Sub

    Sub Commit()

        mmsTransaction.Commit()

    End Sub

    Sub Rollback()

        If mmsCon.State = ConnectionState.Open Then

            mmsTransaction.Rollback()

        End If

    End Sub


    Sub AddParameters(ByRef cmd As SqlCommand, ByVal ListParam As List(Of ParamClass))

        Dim p As ParamClass

        For Each p In ListParam

            Dim sql_p As SqlParameter = New SqlParameter(p.ParamName, p.ParamValue)

            cmd.Parameters.Add(sql_p)
        Next

    End Sub


    Function GetExecuteNum(ByVal cmd As String) As Integer

        Dim iRet As Integer = 0

        Dim mmsCmd As SqlCommand = New SqlCommand(cmd, mmsCon)

        mmsCmd.Transaction = mmsTransaction

        iRet = mmsCmd.ExecuteNonQuery()

        Return iRet

    End Function

    Function GetExecuteNum(ByVal cmd As String, ByVal ListPara As List(Of ParamClass)) As Integer

        Dim iRet As Integer = 0

        Dim mmsCmd As SqlCommand = New SqlCommand(cmd, mmsCon)

        mmsCmd.Parameters.Clear()

        mmsCmd.Transaction = mmsTransaction

        AddParameters(mmsCmd, ListPara)

        iRet = mmsCmd.ExecuteNonQuery()

        Return iRet

    End Function

    Function GetDataTable(ByVal cmd As String, ByVal ListPara As List(Of ParamClass)) As DataTable

        Dim dtRet As DataTable = New DataTable

        Dim mmsCmd As SqlCommand = New SqlCommand(cmd, mmsCon)

        AddParameters(mmsCmd, ListPara)

        mmsCmd.Transaction = mmsTransaction

        Dim adapter As SqlDataAdapter = New SqlDataAdapter(mmsCmd)

        adapter.Fill(dtRet)

        Return dtRet

    End Function

    Function GetDataTable(ByVal cmd As String) As DataTable

        Dim dtRet As DataTable = New DataTable

        Dim mmsCmd As SqlCommand = New SqlCommand(cmd, mmsCon)

        mmsCmd.Transaction = mmsTransaction

        Dim adapter As SqlDataAdapter = New SqlDataAdapter(mmsCmd)

        adapter.Fill(dtRet)

        Return dtRet

    End Function


    Function GetExecuteNum(ByVal cmd As String, ByVal ListPara As List(Of SqlParameter)) As Integer

        Dim iRet As Integer = 0

        Dim mmsCmd As SqlCommand = New SqlCommand(cmd, mmsCon)

        mmsCmd.Transaction = mmsTransaction


        For Each p As SqlParameter In ListPara
            mmsCmd.Parameters.Add(p)
        Next

        iRet = mmsCmd.ExecuteNonQuery()

        Return iRet

    End Function


    Function GetExecuteNum_PSL(ByVal cmd As String, ByVal listParam As List(Of ParamClass)) As Integer

        Dim iRet As Integer = 0

        ReplaceSQLParam(cmd, listParam)

        Dim mmsCmd As SqlCommand = New SqlCommand(cmd, mmsCon)

        mmsCmd.Transaction = mmsTransaction

        iRet = mmsCmd.ExecuteNonQuery()

        Return iRet

    End Function

    Function GetDataTable_PSL(ByVal cmd As String, ByVal ListPara As List(Of ParamClass)) As DataTable

        Dim dtRet As DataTable = New DataTable

        ReplaceSQLParam(cmd, ListPara)

        Dim mmsCmd As SqlCommand = New SqlCommand(cmd, mmsCon)

        mmsCmd.Transaction = mmsTransaction

        Dim adapter As SqlDataAdapter = New SqlDataAdapter(mmsCmd)

        adapter.Fill(dtRet)

        Return dtRet

    End Function


    Public Shared Sub ReplaceSQLParam(ByRef Cmd As String, ByVal listParam As List(Of ParamClass))


        For Each uParamClass As ParamClass In listParam

            Cmd = Cmd.Replace(uParamClass.ParamName, "'" & uParamClass.ParamValue.ToString().Replace("'", "") & "'")

        Next

    End Sub



End Class

Public Class ParamClass

    Sub New()

    End Sub

    'Implements IEquatable(Of ParamClass)

    Sub New(ByVal _ParamName As String, ByVal _ParamValue As Object)

        ParamName = _ParamName
        ParamValue = _ParamValue

    End Sub

    Private _ParamName As String

    Public Property ParamName As String
        Get
            Return _ParamName
        End Get
        Set(ByVal Value As String)
            _ParamName = Value
        End Set
    End Property

    Private _ParamValue As Object

    Public Property ParamValue As Object
        Get
            Return _ParamValue
        End Get
        Set(ByVal Value As Object)
            _ParamValue = Value
        End Set
    End Property

End Class
